/**
 * 数据库加密迁移脚本
 * EndoSight-UC 医疗AI系统 - 焦土协议 Phase 3
 *
 * 将现有数据库迁移到加密格式
 */

import sqlite3 from 'sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';
import { createSecureLogger } from './src/utils/secureLoggingConfig.js';
import { encrypt, decrypt, getEncryptionKey } from './src/config/encryptedDatabase.js';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 安全日志记录器
const logger = createSecureLogger({
  service: 'DBMigration',
  logLevel: 'info'
});

// 数据库路径
const dbPath = path.join(__dirname, 'data/endo_sight_uc.db');

// 需要加密的敏感字段配置
const sensitiveFields = {
  doctors: ['password', 'email', 'license_number'],
  patients: ['name', 'id_card', 'phone', 'email', 'address'],
  scoring_records: ['doctor_notes', 'patient_complaints']
};

/**
 * 执行数据库迁移
 */
const migrateDatabaseToEncrypted = async () => {
  logger.info('=== 开始数据库加密迁移 ===');
  logger.info('迁移时间:', new Date().toISOString());

  try {
    // 验证加密密钥
    const encryptionKey = getEncryptionKey();
    logger.info('加密密钥验证成功', {
      keyLength: encryptionKey.length
    });

    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(dbPath, async (err) => {
        if (err) {
          logger.logError(err, { operation: 'database_connection' });
          return reject(err);
        }

        logger.info('数据库连接成功，开始迁移');

        try {
          // 开始迁移事务
          await executeMigration(db);
          resolve({ success: true });
        } catch (migrationError) {
          logger.logError(migrationError, { operation: 'migration_execution' });
          reject(migrationError);
        } finally {
          db.close();
        }
      });
    });

  } catch (error) {
    logger.logError(error, { operation: 'migration_setup' });
    throw error;
  }
};

/**
 * 执行具体的迁移操作
 */
const executeMigration = async (db) => {
  return new Promise((resolve, reject) => {
    db.serialize(() => {
      // 开始事务
      logger.info('开始迁移事务');
      db.run('BEGIN TRANSACTION');

      let migrationSteps = 0;
      let completedSteps = 0;
      const migrationResults = {};

      // 计算总迁移步骤
      for (const table of Object.keys(sensitiveFields)) {
        db.get(`SELECT COUNT(*) as count FROM ${table}`, (err, row) => {
          if (!err && row.count > 0) {
            migrationSteps += row.count;
          }
        });
      }

      // 迁移医生表敏感数据
      migrateDoctorsTable(db, migrationResults, () => {
        // 迁移患者表敏感数据
        migratePatientsTable(db, migrationResults, () => {
          // 迁移评分记录表敏感数据
          migrateScoringRecordsTable(db, migrationResults, () => {
            // 提交事务
            db.run('COMMIT', (err) => {
              if (err) {
                logger.logError(err, { operation: 'commit_transaction' });
                return db.run('ROLLBACK');
              }

              logger.info('✅ 数据库加密迁移完成', migrationResults);
              resolve(migrationResults);
            });
          });
        });
      });
    });
  });
};

/**
 * 迁移医生表数据
 */
const migrateDoctorsTable = (db, results, callback) => {
  logger.info('开始迁移医生表敏感数据');

  db.all('SELECT id, username, password, email, license_number FROM doctors', (err, rows) => {
    if (err) {
      logger.logError(err, { operation: 'select_doctors' });
      return callback();
    }

    if (rows.length === 0) {
      logger.info('医生表无数据需要迁移');
      results.doctors = { migrated: 0, skipped: 0 };
      return callback();
    }

    let migrated = 0;
    let skipped = 0;

    rows.forEach((doctor, index) => {
      const updates = [];
      const params = [];

      // 加密密码
      if (doctor.password && !doctor.password.startsWith('{')) {
        const encryptedPassword = encrypt(doctor.password, getEncryptionKey());
        updates.push('password = ?');
        params.push(JSON.stringify(encryptedPassword));
      }

      // 加密邮箱
      if (doctor.email && !doctor.email.startsWith('{')) {
        const encryptedEmail = encrypt(doctor.email, getEncryptionKey());
        updates.push('email = ?');
        params.push(JSON.stringify(encryptedEmail));
      }

      // 加密执业证号
      if (doctor.license_number && !doctor.license_number.startsWith('{')) {
        const encryptedLicense = encrypt(doctor.license_number, getEncryptionKey());
        updates.push('license_number = ?');
        params.push(JSON.stringify(encryptedLicense));
      }

      if (updates.length > 0) {
        params.push(doctor.id);
        const updateSQL = `UPDATE doctors SET ${updates.join(', ')} WHERE id = ?`;

        db.run(updateSQL, params, function(err) {
          if (err) {
            logger.warn('医生数据迁移失败', {
              doctorId: doctor.id,
              error: err.message
            });
            skipped++;
          } else {
            migrated++;
            logger.debug('医生数据迁移成功', {
              doctorId: doctor.id,
              username: doctor.username,
              fieldsEncrypted: updates.length
            });
          }

          if (index === rows.length - 1) {
            results.doctors = { migrated, skipped };
            logger.info('医生表迁移完成', { migrated, skipped });
            callback();
          }
        });
      } else {
        skipped++;
        if (index === rows.length - 1) {
          results.doctors = { migrated, skipped };
          logger.info('医生表迁移完成', { migrated, skipped });
          callback();
        }
      }
    });
  });
};

/**
 * 迁移患者表数据
 */
const migratePatientsTable = (db, results, callback) => {
  logger.info('开始迁移患者表敏感数据');

  // 检查患者表是否存在
  db.get("SELECT name FROM sqlite_master WHERE type='table' AND name='patients'", (err, tableExists) => {
    if (err || !tableExists) {
      logger.info('患者表不存在，跳过迁移');
      results.patients = { migrated: 0, skipped: 0 };
      return callback();
    }

    db.all('SELECT id, name, id_card, phone, email, address FROM patients', (err, rows) => {
      if (err) {
        logger.logError(err, { operation: 'select_patients' });
        return callback();
      }

      if (rows.length === 0) {
        logger.info('患者表无数据需要迁移');
        results.patients = { migrated: 0, skipped: 0 };
        return callback();
      }

      let migrated = 0;
      let skipped = 0;

      rows.forEach((patient, index) => {
        const updates = [];
        const params = [];

        // 加密各个敏感字段
        ['name', 'id_card', 'phone', 'email', 'address'].forEach(field => {
          if (patient[field] && !patient[field].startsWith('{')) {
            const encrypted = encrypt(patient[field], getEncryptionKey());
            updates.push(`${field} = ?`);
            params.push(JSON.stringify(encrypted));
          }
        });

        if (updates.length > 0) {
          params.push(patient.id);
          const updateSQL = `UPDATE patients SET ${updates.join(', ')} WHERE id = ?`;

          db.run(updateSQL, params, function(err) {
            if (err) {
              logger.warn('患者数据迁移失败', {
                patientId: patient.id,
                error: err.message
              });
              skipped++;
            } else {
              migrated++;
            }

            if (index === rows.length - 1) {
              results.patients = { migrated, skipped };
              logger.info('患者表迁移完成', { migrated, skipped });
              callback();
            }
          });
        } else {
          skipped++;
          if (index === rows.length - 1) {
            results.patients = { migrated, skipped };
            logger.info('患者表迁移完成', { migrated, skipped });
            callback();
          }
        }
      });
    });
  });
};

/**
 * 迁移评分记录表数据
 */
const migrateScoringRecordsTable = (db, results, callback) => {
  logger.info('开始迁移评分记录表敏感数据');

  // 检查评分记录表是否存在
  db.get("SELECT name FROM sqlite_master WHERE type='table' AND name='scoring_records'", (err, tableExists) => {
    if (err || !tableExists) {
      logger.info('评分记录表不存在，跳过迁移');
      results.scoring_records = { migrated: 0, skipped: 0 };
      return callback();
    }

    db.all('SELECT id, doctor_notes, patient_complaints FROM scoring_records WHERE doctor_notes IS NOT NULL OR patient_complaints IS NOT NULL', (err, rows) => {
      if (err) {
        logger.logError(err, { operation: 'select_scoring_records' });
        return callback();
      }

      if (rows.length === 0) {
        logger.info('评分记录表无敏感数据需要迁移');
        results.scoring_records = { migrated: 0, skipped: 0 };
        return callback();
      }

      let migrated = 0;
      let skipped = 0;

      rows.forEach((record, index) => {
        const updates = [];
        const params = [];

        // 加密医生备注
        if (record.doctor_notes && !record.doctor_notes.startsWith('{')) {
          const encryptedNotes = encrypt(record.doctor_notes, getEncryptionKey());
          updates.push('doctor_notes = ?');
          params.push(JSON.stringify(encryptedNotes));
        }

        // 加密患者主诉
        if (record.patient_complaints && !record.patient_complaints.startsWith('{')) {
          const encryptedComplaints = encrypt(record.patient_complaints, getEncryptionKey());
          updates.push('patient_complaints = ?');
          params.push(JSON.stringify(encryptedComplaints));
        }

        if (updates.length > 0) {
          params.push(record.id);
          const updateSQL = `UPDATE scoring_records SET ${updates.join(', ')} WHERE id = ?`;

          db.run(updateSQL, params, function(err) {
            if (err) {
              logger.warn('评分记录迁移失败', {
                recordId: record.id,
                error: err.message
              });
              skipped++;
            } else {
              migrated++;
            }

            if (index === rows.length - 1) {
              results.scoring_records = { migrated, skipped };
              logger.info('评分记录表迁移完成', { migrated, skipped });
              callback();
            }
          });
        } else {
          skipped++;
          if (index === rows.length - 1) {
            results.scoring_records = { migrated, skipped };
            logger.info('评分记录表迁移完成', { migrated, skipped });
            callback();
          }
        }
      });
    });
  });
};

/**
 * 执行迁移
 */
const main = async () => {
  try {
    console.log('🔐 开始执行数据库加密迁移...');
    console.log('⚠️  请确保已备份原始数据库文件');

    const result = await migrateDatabaseToEncrypted();

    console.log('✅ 数据库加密迁移完成');
    console.log('📊 迁移结果:', JSON.stringify(result, null, 2));
    console.log('');
    console.log('🔒 安全提示:');
    console.log('   1. 所有敏感数据已使用AES-256-GCM加密');
    console.log('   2. 加密密钥存储在环境变量DB_ENCRYPTION_KEY中');
    console.log('   3. 原始明文数据已被加密数据替换');
    console.log('   4. 系统现在使用加密数据库访问模式');

  } catch (error) {
    console.error('❌ 数据库加密迁移失败:', error.message);
    logger.logError(error, { operation: 'main_migration' });
    process.exit(1);
  }
};

// 执行脚本
if (import.meta.url === `file://${process.argv[1]}`) {
  main();
}

export { migrateDatabaseToEncrypted };